﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using DAL.DFactory;
using DAL.DHelpers;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace DAL.DCollector
{
    public class DBCollector
    {

        private DatabaseProvider name;
        public static DBCollector Instance = new DBCollector(DatabaseProvider.SQLServer);
        private MappingTable table;
        public DBCollector(DatabaseProvider name)
        {
            this.name = name;

        }
        public DBCollector(DatabaseProvider name, MappingTable table)
        {
            this.name = name;
            this.table = table;

        }
        #region Tập hợp thao tác SELECT
        public DataTable SelectSQL(String sql)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))

                return DbManager.ExecuteDataTable(sql);
        }

        public DataTable SelectTable(String table)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT * FROM " + table);
        }

        public DataTable SelectCols(String table, string Cols)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT " + Cols + " FROM " + table);
        }

        public DataTable SelectColsByWhere(String table, string Cols, string Condition)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT " + Cols + " FROM " + table + "WHERE " + Condition);
        }

        public DataTable SelectAllByWhere(String table, string Condition)
        {
            using (DatabaseManager DbManager = new DatabaseManager(this.name))
                return DbManager.ExecuteDataTable("SELECT * FROM " + table + "WHERE " + Condition);
        }

        public DataTable SelectAllByColunmnInArrays(string table, string ColumnName, string[] ColArrays)
        {
            String strQuery = String.Empty;
            strQuery += "SELECT * FROM " + table;
            strQuery += " WHERE " + ColumnName + " IN ( ";
            strQuery += ParamsCreator.instance.GetSeparateString(ColArrays);
            strQuery += ")";
            return DatabaseManager.SqlServerInstance.ExecuteDataTable(strQuery);
        }
        public DataTable SelectAllByColunmnInArrays(string table, string ColumnName, string ColArrays)
        {
            String strQuery = String.Empty;
            strQuery += "SELECT * FROM " + table;
            strQuery += " WHERE " + ColumnName + " IN ( ";
            strQuery += ColArrays;
            strQuery += ")";
            return DatabaseManager.SqlServerInstance.ExecuteDataTable(strQuery);
        }
        public DataTable SelectAllByWhere(string table, string parNames, string parValues, string typeValues)
        {

            String sqlString = String.Empty;
            sqlString += " SELECT * FROM " + table;

            sqlString += GetWhereCondition(parNames.Split(','));
            SqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues, typeValues);
            return DatabaseManager.SqlServerInstance.ExecuteDataTable(sqlString, myParams);
        }

        public DataTable SelectAllByWhere(string table, string[] parNames, object[] parValues)
        {

            String sqlString = String.Empty;
            sqlString += " SELECT * FROM " + table;

            sqlString += GetWhereCondition(parNames);
            SqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues);
            return DatabaseManager.SqlServerInstance.ExecuteDataTable(sqlString, myParams);



        }
        // lay ve so ban ghi cua mot bang of Duyen pro
        public int countRecordofTable(string table)
        {

            // dung 
            int sobanghi = 0;
            string query = " Select count(*) as count from " + table;
            DataTable nametable;
            nametable = SelectSQL(query);
            sobanghi = Convert.ToInt32(getValue(0, 0, nametable));

            return sobanghi;
        }
        public string getValue(int col, int row, DataTable dt)
        {
            return dt.Rows[row].ItemArray[col].ToString();
        }

        #endregion

        #region Tập hợp thao tác DELETE
        public void Delete(String table, String Condition)
        {

            DatabaseManager.SqlServerInstance.ExecuteNonQuery("DELETE FROM " + table + " WHERE " + Condition);
        }

        public void DeleteByWhere(string table, string parNames, string parValues, string typeValues)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;

            sqlString += GetWhereCondition(parNames.Split(','));
            SqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues, typeValues);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);

        }
        public void DeleteByColumnInArrays(string table, string ColumnName, string arraysValue)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;
            sqlString += " WHERE " + ColumnName + " IN (" + arraysValue + ")";
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString);

        }
        public void DeleteByColumnInArrays(string table, string ColumnName, string[] Values)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;
            sqlString += " WHERE " + ColumnName + " IN (" + ParamsCreator.instance.GetSeparateString(Values) + ")";
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString);

        }

        public void DeleteByWhere(string table, string[] parNames, object[] parValues)
        {

            String sqlString = String.Empty;
            sqlString += " DELETE FROM " + table;

            sqlString += GetWhereCondition(parNames);
            SqlParameter[] myParams = ParamsCreator.instance.GetParams(parNames, parValues);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);



        }
        #endregion

        #region Tập hợp thao tác INSERT
        public void Insert(String table, string[] Cols, object[] parValues)
        {
            this.table = new MappingTable(table, true);
            String sqlString = String.Empty;
            sqlString += " INSERT INTO " + table;
            sqlString += GetValuesInsert(table);
            string[] parNames;
            if (this.table.HasColumnIsIdentity == false)
            { parNames = this.table.Columns.Keys.ToArray<string>(); }
            else
            {
                parNames = new string[this.table.Columns.Count - 1];
                int n = 0;
                foreach (Column p in this.table.Columns.Values)
                {
                    if (p.IsPrimaryKey == false)
                    {
                        parNames[n] = p.ColumnName;
                        n++;
                    }
                }
            }
            SqlParameter[] myParams = ParamsCreator.instance.GetInsertParams(parNames, Cols, parValues);

            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);
        }
        public void Insert(string table, Column[] Values)
        {

            String sqlString = String.Empty;
            sqlString += " INSERT INTO " + table + "(";
            sqlString += GetValuesInsert(Values);
            sqlString += ")";

            SqlParameter[] myParams = ParamsCreator.instance.GetParams(Values);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);

        }
        #endregion

        #region Tập hợp thao tác UPDATE
        public void Update(String table, string[] ColsUpdate, object[] parUpdate, string[] ColsWhere, object[] parWhere)
        {
            this.table = new MappingTable(table, true);
            String sqlString = String.Empty;
            sqlString += " UPDATE  " + table + "  SET ";
            sqlString += GetValuesUpdate(ColsUpdate);
            sqlString += GetWhereCondition(ColsWhere);
            SqlParameter[] myParams1 = ParamsCreator.instance.GetParams(ColsUpdate, parUpdate);
            SqlParameter[] myParams2 = ParamsCreator.instance.GetParams(ColsWhere, parWhere);
            SqlParameter[] myParams = ParamsCreator.instance.GetParams(myParams1, myParams2);

            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);
        }
        public void Update(string table, Column[] Values, string[] ColsWhere, object[] parWhere)
        {

            String sqlString = String.Empty;
            sqlString += " UPDATE  " + table + " SET ";
            sqlString += GetValuesUpdate(Values);
            sqlString += "";
            sqlString += GetWhereCondition(ColsWhere);
            SqlParameter[] myParams1 = ParamsCreator.instance.GetParams(Values);
            SqlParameter[] myParams2 = ParamsCreator.instance.GetParams(ColsWhere, parWhere);
            SqlParameter[] myParams = ParamsCreator.instance.GetParams(myParams1, myParams2);
            DatabaseManager.SqlServerInstance.ExecuteNonQuery(sqlString, myParams);
        }
        #endregion

        #region Các hàm Tiện ích
        private String GetWhereCondition(string[] parNames)
        {
            String strSql = String.Empty;
            if (parNames.Length > 0)
            {
                strSql += " WHERE ";
                foreach (string p in parNames)
                {
                    strSql += p + " = @" + p + " AND ";

                }
                strSql += " 1 = 1 ";
            }
            return strSql;
        }
        private String GetValuesInsert(String table)
        {
            this.table = new MappingTable(table, true);
            String strSql = String.Empty;
            string str1 = String.Empty;
            string str2 = String.Empty;
            if (this.table.Columns.Count > 0)
            {
                int n = this.table.Columns.Count;
                str1 += " ( ";
                str2 += " ( ";
                foreach (Column p in this.table.Columns.Values)
                {
                    if (p.IsPrimaryKey == false)
                    {

                        if (n == 2)
                        {
                            str1 += p.ColumnName;
                            str2 += " @" + p.ColumnName;
                        }

                        else
                        {
                            str1 += p.ColumnName + " , ";
                            str2 += " @" + p.ColumnName + " , ";
                        }
                        n = n - 1;
                    }

                }
                str1 += " )";
                str2 += " )";
                strSql = str1 + " VALUES " + str2;
                return strSql;

            }
            return strSql;
        }
        private String GetValuesInsert(Column[] Values)
        {
            int n = Values.Length;
            String str = String.Empty;
            int i = 0;
            for (; i < n - 1; i++)
            {
                str += Values[i].ColumnName + ",";
            }
            str += Values[i].ColumnName;
            str += ")Values(";
            i = 0;
            for (; i < n - 1; i++)
            {
                str += "@" + Values[i].ColumnName + ",";
            }
            str += "@" + Values[i].ColumnName;
            return str;
        }
        private String GetValuesUpdate(String[] Values)
        {
            int n = Values.Length;
            String str = String.Empty;
            int i = 0;
            for (; i < n - 1; i++)
            {
                str += Values[i] + "= @" + Values[i] + ",";
            }
            str += Values[i] + "= @" + Values[i];
            return str;


        }
        private String GetValuesUpdate(Column[] Values)
        {
            int n = Values.Length;
            String str = String.Empty;
            int i = 0;
            for (; i < n - 1; i++)
            {
                str += Values[i].ColumnName + "= @" + Values[i].ColumnName + ",";
            }
            str += Values[i].ColumnName + "= @" + Values[i].ColumnName;
            return str;


        }
        #endregion

        #region THAO TÁC VỚI STORED PROCEDURE ( QUAN TRONG - DỰ ÁN LAKE-HOTEL)


        public int StoredProcedureSelectCountRows(string table)
        {
            string[] parName = new string[1];
            parName[0] = "table";

            string[] parvalue = new string[1];
            parvalue[0] = table;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return Convert.ToInt32(DatabaseManager.SqlServerInstance.CallStoredProcedure("selectcountrow", p, true).Rows[0]["count"].ToString());
        }
        // truyen vao ten bang va danh sach cac cot cach nhau boi dau phay
        // StoredProcedureSelectByColumns("product", "price,name");
        public DataTable StoredProcedureSelectByColumns(string table, string columns, string proceduceName)
        {
            string[] parName = new string[2];
            parName[0] = "table";
            parName[1] = "columns";
            string[] parvalue = new string[2];
            parvalue[0] = table;
            parvalue[1] = columns;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(proceduceName, p, true);
        }
        public DataTable StoredProcedureSelectByColumns(string table, string columns, string orderby, string proceduceName)
        {
            string[] parName = new string[3];
            parName[0] = "table";
            parName[1] = "columns";
            parName[2] = "order";
            string[] parvalue = new string[3];
            parvalue[0] = table;
            parvalue[1] = columns;
            parvalue[2] = orderby;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(proceduceName, p, true);
        }
        public DataTable StoredProcedureSelectWhereByID(string table, string columns, string WhereColumn, int idValue, string procedureName)
        {
            string[] parName = new string[4];
            parName[0] = "table";
            parName[1] = "columns";
            parName[2] = "wherecolumn";
            parName[3] = "id";
            object[] parvalue = new object[4];
            parvalue[0] = table;
            parvalue[1] = columns;
            parvalue[2] = WhereColumn;
            parvalue[3] = idValue;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }
        public DataTable StoredProcedureSelectWhereByID(string table, string columns, string WhereColumn, string idValue, string procedureName)
        {
            string[] parName = new string[4];
            parName[0] = "table";
            parName[1] = "columns";
            parName[2] = "wherecolumn";
            parName[3] = "id";
            object[] parvalue = new object[4];
            parvalue[0] = table;
            parvalue[1] = columns;
            parvalue[2] = WhereColumn;
            parvalue[3] = idValue;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }

        public DataTable StoredProcedureSelectWhereByID(string table, string WhereColumn, int whereValue, string procedureName)
        {
            string[] parName = new string[3];
            parName[0] = "table";

            parName[1] = "whereColumn";
            parName[2] = "whereValue";
            object[] parvalue = new object[3];
            parvalue[0] = table;

            parvalue[1] = WhereColumn;
            parvalue[2] = whereValue;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }
        public DataTable StoredProcedureSelectWhereByID(string table, string WhereColumn, string whereValue, string procedureName)
        {
            string[] parName = new string[3];
            parName[0] = "table";

            parName[1] = "whereColumn";
            parName[2] = "whereValue";
            object[] parvalue = new object[3];
            parvalue[0] = table;

            parvalue[1] = WhereColumn;
            parvalue[2] = whereValue;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }
        // columns la chuoi cac cot cach nhau boi day phay ,GruopID la chuoi cac ID cach nhau boi dau phay
        public DataTable StoredProcedureSelectWhereByGroupID(string table, string columns, string WhereColumn, string GroupID)
        {
            string[] parName = new string[4];
            parName[0] = "table";
            parName[1] = "columns";
            parName[2] = "wherecolumn";
            parName[3] = "groupid";
            object[] parvalue = new object[4];
            parvalue[0] = table;
            parvalue[1] = columns;
            parvalue[2] = WhereColumn;
            parvalue[3] = GroupID;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure("selectwherebygroupid", p, true);
        }
        public DataTable StoredProcedureInsertByRows(string table, string procedureName, string columns, List<string> myvalue, bool b)
        {
            string[] parName = new string[3];
            parName[0] = "table";
            parName[1] = "columns";
            parName[2] = "values";

            string values = string.Empty;
            for (int i = 0; i < myvalue.Count; i++)
            {
                if (i == myvalue.Count - 1)
                    values += "(" + myvalue[i].ToString() + ")";
                else
                    values += "(" + myvalue[i].ToString() + "),";
            }

            string[] parvalue = new string[3];
            parvalue[0] = table;
            parvalue[1] = columns;
            parvalue[2] = values;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }
        public DataTable StoredProcedureInsertByRows(string table, string procedureName, List<string> myvalue, bool b)
        {
            string[] parName = new string[2];
            parName[0] = "table";
            parName[1] = "values";

            string values = string.Empty;
            for (int i = 0; i < myvalue.Count; i++)
            {
                if (i == myvalue.Count - 1)
                    values += "(" + myvalue[i].ToString() + ")";
                else
                    values += "(" + myvalue[i].ToString() + "),";
            }

            string[] parvalue = new string[2];
            parvalue[0] = table;
            parvalue[1] = values;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }
        public DataTable StoredProcedureDeleteByRows(string table, string WhereColumn, string GroupID)
        {
            string[] parName = new string[3];
            parName[0] = "table";
            parName[1] = "wherecolumn";
            parName[2] = "groupid";



            string[] parvalue = new string[3];
            parvalue[0] = table;
            parvalue[1] = WhereColumn;
            parvalue[2] = GroupID;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure("deletebyrows", p, false);
        }


        public DataTable StoredProcedureUpdateByRow(string table, string WhereColumn, string ID, string columns, string values)
        {
            string[] parName = new string[4];
            parName[0] = "table";

            parName[1] = "wherecolumn";
            parName[2] = "id";
            parName[3] = "sets";

            string sets = string.Empty;
            string[] cols = columns.Split(',');
            string[] vals = values.Split(',');
            for (int i = 0; i < cols.Length; i++)
            {
                if (i == cols.Length - 1)
                    sets += cols[i] + " = '" + vals[i] + "'";
                else
                    sets += cols[i] + " = '" + vals[i] + "' , ";
            }

            object[] parvalue = new object[4];
            parvalue[0] = table;
            parvalue[1] = WhereColumn;
            parvalue[2] = ID;
            parvalue[3] = sets;
            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure("updatepassword", p, false);
        }

        // NGUYEN VIET HAI - UPDATE CODE 1/5/2013

        public DataTable StoredProcedureSelectTable(string table, string procedureName)
        {
            string[] parName = new string[1];
            parName[0] = "table";

            object[] parvalue = new object[1];
            parvalue[0] = table;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }


        public DataTable StoredProcedureSelectBoxTable(string table1, string table2, int boxproductid, string procedureName)
        {
            string[] parName = new string[3];
            parName[0] = "table1";
            parName[1] = "table2";
            parName[2] = "boxproductid";

            object[] parvalue = new object[3];
            parvalue[0] = table1;
            parvalue[1] = table2;
            parvalue[2] = boxproductid;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }

        // NGUYEN VIET HAI - UPDATE CODE 1/7/2013
        public DataTable StoredProcedureInsertProduct(string table, string myvalue, string procedureName)
        {
            string[] parName = new string[2];
            parName[0] = "table";
            parName[1] = "values";

            string values = myvalue;


            string[] parvalue = new string[2];
            parvalue[0] = table;
            parvalue[1] = values;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, false);
        }

        public DataTable StoredProcedureDeleteByRowProduct(string table, int productId, string procedureName)
        {
            string[] parName = new string[2];
            parName[0] = "table";
            parName[1] = "idproduct";
            object[] parvalue = new object[2];
            parvalue[0] = table;
            parvalue[1] = productId;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, false);
        }
        public DataTable StoredProcedureGetTableByID(string table, int productId, string procedureName)
        {
            string[] parName = new string[2];
            parName[0] = "table";
            parName[1] = "idproduct";
            object[] parvalue = new object[2];
            parvalue[0] = table;
            parvalue[1] = productId;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }

        public DataTable StoredProcedureGetTableByTypeWhereID(string table, int type, int productId, string procedureName)
        {
            string[] parName = new string[3];
            parName[0] = "table";
            parName[1] = "idproduct";
            parName[2] = "type";
            object[] parvalue = new object[3];
            parvalue[0] = table;
            parvalue[1] = productId;
            parvalue[2] = type;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }


        public DataTable StoredProcedureLogin(string procedureName, string username, string password)
        {
            string[] parName = new string[2];
            parName[0] = "username";
            parName[1] = "password";
            object[] parvalue = new object[2];
            parvalue[0] = username;
            parvalue[1] = password;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }

        public DataTable StoredProcedureForgetPassword(string procedureName, string username, string email)
        {
            string[] parName = new string[2];
            parName[0] = "username";
            parName[1] = "email";
            object[] parvalue = new object[2];
            parvalue[0] = username;
            parvalue[1] = email;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, true);
        }
        public DataTable CallStored(string procedureName, string sql, bool b)
        {
            string[] parName = new string[1];
            parName[0] = "param1";

            string[] parvalue = new string[1];
            parvalue[0] = sql;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }
        public long getNextID(string table, string column)
        {
            DataTable dt = DAL.DCollector.DBCollector.Instance.CallStored("callstored", "select top 1 * from " + table + " ORDER BY " + column + "  DESC ", true);
            if (dt.Rows.Count > 0)
            {
                string lastid = dt.Rows[0][column].ToString();
                return Convert.ToInt32(lastid);
            }
            return 0;
        }

        /**************************************************/

        public DataTable StoredSelectTableWhereIDSort(string tableName, string column, string id, string orderbyColumn, string sort)
        {

            string sql = "select * from " + tableName + " where " + column + " = " + id + " Order by " + orderbyColumn + " " + sort;

            return CallStored("callstored", sql, true);

        }

        /**************************************************/

        public DataTable StoredSelectTableWhereID(string tableName, string getColumn, string column, string id)
        {

            string sql = "select " + getColumn + " from " + tableName + " where " + column + " = " + id;

            return CallStored("callstored", sql, true);

        }

        /**************************************************/

        public void StoredDelete(string table, string id)
        {
            string column = table.Replace("tbl_", "") + "_ID";
            string sql = " delete from " + table + " where " + column + " = " + id;
            CallStored("callstored", sql, false);
        }

        /**************************************************/

        public DataTable StoredSelectTableWhereID(string tableName, string column, string id)
        {

            string sql = "select * from " + tableName + " where " + column + " = " + id;

            return CallStored("callstored", sql, true);

        }

        #endregion
        /**************************************************/

        public DataTable CallStored(string sql, bool b)
        {
            string[] parName = new string[1];
            parName[0] = "param1";

            string[] parvalue = new string[1];
            parvalue[0] = sql;

            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure("CallSQL", p, b);
        }

        // Goi stored 5 tham so

        public DataTable CallStored5Param(string procedureName, string param1, string param2, string param3, string param4, string param5, bool b)
        {
            string[] parName = new string[5];
            parName[0] = "param1";
            parName[1] = "param2";
            parName[2] = "param3";
            parName[3] = "param4";
            parName[4] = "param5";

            string[] parvalue = new string[5];
            parvalue[0] = param1;
            parvalue[1] = param2;
            parvalue[2] = param3;
            parvalue[3] = param4;
            parvalue[4] = param5;


            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }
        public DataTable CallStored6Param(string procedureName, string param1, string param2, string param3, string param4, string param5,string param6, bool b)
        {
            string[] parName = new string[6];
            parName[0] = "param1";
            parName[1] = "param2";
            parName[2] = "param3";
            parName[3] = "param4";
            parName[4] = "param5";
            parName[5] = "param6";

            string[] parvalue = new string[6];
            parvalue[0] = param1;
            parvalue[1] = param2;
            parvalue[2] = param3;
            parvalue[3] = param4;
            parvalue[4] = param5;
            parvalue[5] = param6;


            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }

        // Goi stored 7 tham so

        public DataTable CallStored7Param(string procedureName, string param1, string param2, string param3, string param4, string param5, string param6, string param7, bool b)
        {
            string[] parName = new string[7];
            parName[0] = "param1";
            parName[1] = "param2";
            parName[2] = "param3";
            parName[3] = "param4";
            parName[4] = "param5";
            parName[5] = "param6";
            parName[6] = "param7";

            string[] parvalue = new string[7];
            parvalue[0] = param1;
            parvalue[1] = param2;
            parvalue[2] = param3;
            parvalue[3] = param4;
            parvalue[4] = param5;
            parvalue[5] = param6;
            parvalue[6] = param7;


            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }

        // Goi stored 9 tham so

        public DataTable CallStored9Param(string procedureName, string param1, string param2, string param3, string param4, string param5, string param6, string param7, string param8, string param9, bool b)
        {
            string[] parName = new string[9];
            parName[0] = "param1";
            parName[1] = "param2";
            parName[2] = "param3";
            parName[3] = "param4";
            parName[4] = "param5";
            parName[5] = "param6";
            parName[6] = "param7";
            parName[7] = "param8";
            parName[8] = "param9";

            string[] parvalue = new string[9];
            parvalue[0] = param1;
            parvalue[1] = param2;
            parvalue[2] = param3;
            parvalue[3] = param4;
            parvalue[4] = param5;
            parvalue[5] = param6;
            parvalue[6] = param7;
            parvalue[7] = param8;
            parvalue[8] = param9;


            SqlParameter[] p = ParamsCreator.instance.GetParams(parName, parvalue);

            return DatabaseManager.SqlServerInstance.CallStoredProcedure(procedureName, p, b);
        }

        /******************************************/

        public DataTable PagingComments5Param(string procedureName, string table, string column, string currentPage, string SizePage, string sort, bool b)
        {
            DataTable dt = CallStored5Param(procedureName, table, column, currentPage, SizePage, sort, b);
            DataView dv = dt.DefaultView;
            dv.RowFilter = "HoiDap_Active = 1";
            return dv.ToTable();
        }
        /******************************************/

        public DataTable PagingComments7Param(string procedureName, string table, string column, string currentPage, string SizePage, string sort, string col1, string valu1, bool b)
        {
            DataTable dt = CallStored7Param(procedureName, table, column, currentPage, SizePage, sort, col1, valu1, b);
            DataView dv = dt.DefaultView;
            dv.RowFilter = "HoiDap_Active = 1";
            return dv.ToTable();
        }


        /******************************************/

        public DataTable Paging5Param(string procedureName, string table, string column, string currentPage, string SizePage, string sort, bool b)
        {
            return CallStored5Param(procedureName, table, column, currentPage, SizePage, sort, b);

        }

        /******************************************/

        public DataTable Paging7Param(string procedureName, string table, string column, string currentPage, string SizePage, string sort, string col1, string valu1, bool b)
        {
            return CallStored7Param(procedureName, table, column, currentPage, SizePage, sort, col1, valu1, b);
        }

        /******************************************/

        public DataTable Paging9Param(string procedureName, string table, string column, string currentPage, string SizePage, string sort, string col1, string valu1, string col2, string valu2, bool b)
        {
            return CallStored9Param(procedureName, table, column, currentPage, SizePage, sort, col1, valu1, col2, valu2, b);
        }
        public DataTable PagingAdvanceSearch(string procedureName, string table, string column, string currentPage, string SizePage, string sort, string condition, bool b)
        {
            return CallStored6Param(procedureName, table, column, currentPage, SizePage, sort, condition, b);
        }
        /******************************************/

        public DataTable StoredSelectAllTable(string table)
        {
            string sql = "select * from  " + table;
            return CallStored(sql, true);
        }


        /******************************************/

        public DataTable StoredSelectAllTableOrderByCol(string table, string OrderCol, string sort)
        {
            string sql = "select *  from  " + table + " Order By " + OrderCol + " " + sort;
            return CallStored(sql, true);

        }


        /******************************************/

        public DataTable UpdateWhereID(string table, string orderCol1, string valucol1, string orderCol2, string valucol2)
        {
            string sql = "update " + table + " SET " + orderCol1 + " = '" + valucol1 + "' where " + orderCol2 + " = " + valucol2;
            return CallStored(sql, false);
        }
        /******************************************/

        public DataTable StoredSelectAllTableOrderByDateAndTop(string table, string OrderCol, string top, string sort)
        {
            string sql = "select top " + top + " * from  " + table + " Order By " + OrderCol + " " + sort;
            return CallStored(sql, true);

        }

        /******************************************/

        public DataTable StoredSelectAllTableWhereIDOrderBySort(string table, string WhereCol, string id, string OrderCol, string sort)
        {
            string sql = "select * from " + table + " Where " + WhereCol + " = " + id + "  Order by " + OrderCol + " " + sort;
            return CallStored(sql, true);
        }


        /******************************************/

        public DataTable StoredSelectAllTableWhereID(string table, string WhereCol, string id)
        {
            string sql = "select * from " + table + " Where " + WhereCol + " = " + id;
            return CallStored(sql, true);
        }

        /**************************************************************************/

        public DataTable StoredSelectAllTableWhereName(string table, string WhereCol, string name)
        {
            string sql = "select * from " + table + " Where " + WhereCol + " = '" + name + "'";
            return CallStored(sql, true);
        }
        /******************************************/


        /******************************************/

        public DataTable StoredProcedureEmployeeLogin(string username, string password)
        {
            string sql = " SELECT  *  FROM tbl_Employee WHERE tbl_Employee.Employee_User = '" + username + "' AND tbl_Employee.Employee_Password = '" + password + "'";

            return CallStored(sql, true);
        }

        /*********************************************/
        public DataTable StoredProcedureCheckUsername(string username)
        {
            string sql = "SELECT count (*) as result  FROM tbl_Employee WHERE tbl_Employee.Employee_User = '" + username + "'";

            return CallStored(sql, true);
        }

        /*********************************************/
        public DataTable StoredProcedureCheckUser(string username, string email)
        {
            string sql = "SELECT count (*) as result  FROM tbl_Employee WHERE tbl_Employee.Employee_User = '" + username + "' OR tbl_Employee.Employee_Email = '" + email + "'";

            return CallStored(sql, true);
        }
        /*********************************************/
        public void Insert(string table, List<string> myvalue, bool b)
        {

            string values = string.Empty;
            for (int i = 0; i < myvalue.Count; i++)
            {
                if (i == myvalue.Count - 1)
                    values += "(" + myvalue[i].ToString() + ")";
                else
                    values += "(" + myvalue[i].ToString() + "),";
            }


            string sql = "Insert into " + table + " VALUES " + values;
            CallStored(sql, false);
        }

    }
}
